﻿using Models.Common;
using Models.MVC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;

namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class Bill6Controller : Controller
    {
        // GET: Bill6
        public ActionResult DetailsList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select bi.ID,bi.Code,bi.BiaoZhun,bi.CreateDate,bi.Number,bi.Qty,po.WaiXing,po.XuFangDaiHao,po.Code as JiHuaHao,po.DengJi,po.GuiFanHao,po.HeTongCode
		,po.HeTongQty as HeTongCount,bi.JiHuaOrderID
	,po.GoodsID,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
FROM BillIndex6 as bi inner join PlannedOrder as po on bi.JiHuaOrderID=po.ID 
	left join Goods on po.GoodsID=Goods.ID 
WHERE bi.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string DetailsListAsync(int IndexID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@IndexID",SqlDbType.Int),
            };
            parameters[0].Value = IndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill6BillDetailsList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        // GET: Bill6
        public ActionResult IndexList()
        {
            return View();
        }

        [HttpPost]
        public string IndexListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", int State = 0)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@State",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill6IndexList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexEdit(int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select bi.ID,bi.Code,bi.BiaoZhun,bi.CreateDate,bi.Number,bi.Qty,po.WaiXing,po.XuFangDaiHao,po.Code as JiHuaHao,po.DengJi,po.GuiFanHao,po.HeTongCode
		,po.HeTongQty as HeTongCount,bi.JiHuaOrderID,po.Code as JiHuaOrderCode
	,po.GoodsID,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
FROM BillIndex6 as bi inner join PlannedOrder as po on bi.JiHuaOrderID=po.ID 
	left join Goods on po.GoodsID=Goods.ID 
WHERE bi.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            m.CreateDate = DateTime.Now.ToString("yyyy-MM-dd");
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=5 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            DataTable ddt = FreeADO.DBUtility.DbHelperSQL.Query("select * from DengJi").Tables[0];
            string ds = "";
            foreach (DataRow item in ddt.Rows)
            {
                ds = ds + @"<option value=""" + item["Name"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.DengJi = ds;

            if (ID == 0)
            {
                DataTable mdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillTempletIndex where BillNameID=6 and IsDeleted=0").Tables[0];
                string ms = "";
                foreach (DataRow item in mdt.Rows)
                {
                    ms = ms + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
                }
                ViewBag.MoBan = ms;
            }
            else
            {
                ViewBag.MoBan = "";
            }


            return View();
        }
        [HttpPost]
        public ActionResult IndexEditAsync(Models.MVC.Bill6IndexModel m)
        {
            Result<string> result = Web.Common.Bill6.IndexEdit(m);
            return Json(result);

        }
        [HttpPost]
        public ActionResult IndexDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillIndex6 set IsDeleted=1 where ID in (" + IDs + @")");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult ModifyIndexStateAsync(int ID,int State)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                SqlParameter[] paras ={
                    new SqlParameter("@BillNameID",SqlDbType.Int),
                    new SqlParameter("@IndexID",SqlDbType.Int),
                    new SqlParameter("@Type",SqlDbType.Int),
                    new SqlParameter("@Str",SqlDbType.NVarChar,4000),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                                       };
                paras[0].Value = 6;
                paras[1].Value = ID;
                paras[2].Value = State;
                paras[3].Value = "";
                paras[3].Direction = ParameterDirection.InputOutput;
                paras[4].Direction = ParameterDirection.ReturnValue;

                DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ModifyIndexState", paras, "aaa", 3000);
                if (Convert.ToInt32(paras[4].Value) == 1)
                {
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = 0;
                }
                else
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = paras[3].Value.ToString();
                    result.Data = 0;
                }
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillProceduresEdit(int IndexID = 0,int ID=0)
        {
            ViewBag.IndexID = IndexID;
            ViewBag.ID = ID;
            ViewBag.ProcedureID = 0;
            ViewBag.Remark = "";
            ViewBag.ShowIndex = 0;
            ViewBag.BiaoZhunQty = 1;
            ViewBag.GongYiID = 0;
            if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures6 where IsDeleted=0 and BillIndexID=" + IndexID)) > 0)
            {
                ViewBag.ShowIndex = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select max(ShowIndex) from BillProcedures6 where IsDeleted=0 and BillIndexID=" + IndexID)) + 1;
            }

            if (ID > 0)
            {
                DataTable fdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillProcedures6 where ID=" + ID).Tables[0];
                ViewBag.ProcedureID = fdt.Rows[0]["ProcedureID"].ToString();
                ViewBag.Remark = fdt.Rows[0]["Remark"].ToString();
                ViewBag.ShowIndex = fdt.Rows[0]["ShowIndex"].ToString();
                ViewBag.BiaoZhunQty = fdt.Rows[0]["BiaoZhunQty"].ToString();
                ViewBag.GongYiID = fdt.Rows[0]["GongYiID"].ToString();
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Procedures where IsDeleted=0 and State=1 and BillNameID=6").Tables[0];
            string s = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Procedures = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillProceduresEditAsync(int IndexID = 0, int ID = 0, int ProcedureID = 0, string Remark = "",int ShowIndex=0, decimal BiaoZhunQty = 0,int GongYiID=0)
        {
            Result<int> result = new Result<int>();
            if (BiaoZhunQty <= 0)
            {
                result.Type = AjaxResultType.Error;
                result.Content = "工时标准必须大于0！";
                result.Data = 0;
                return Json(result);
            }
            try
            {
                if (ID == 0)
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures6 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures6 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                    }

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("insert into BillProcedures6 (BillIndexID,ProcedureID,Remark,ShowIndex,BiaoZhunQty,GongYiID) values (" + IndexID + "," + ProcedureID + ",@Remark," + ShowIndex + "," + BiaoZhunQty.ToString() + ","+GongYiID+")", new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                else
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures6 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0 and ID<>" + ID)) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures6 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0 and ID<>" + ID);
                    }
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update BillProcedures6 set ProcedureID=" + ProcedureID + ",Remark=@Remark,ShowIndex=" + ShowIndex + @",BiaoZhunQty=" + BiaoZhunQty.ToString() + ", GongYiID=" + GongYiID + " where ID=" + ID, new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                result.Type = AjaxResultType.Success;
                result.Content = "成功。";
                result.Data = 0;

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillDetailsEdit(int BillProcedureID = 0, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillDetails6 where ID=" + ID).Tables[0];
            Bill6DetailsModel m = new Bill6DetailsModel();
            m.BillProcedureID = BillProcedureID;
            m.GongYi= FreeADO.DBUtility.DbHelperSQL.GetSingle("select Remark from BillProcedures6 where ID=" + BillProcedureID).ToString();
            m.InQty = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select dbo.Fn_Value_GetOutQty(6," + BillProcedureID + ")"));
            m.OutQty = m.InQty;
            m.OperatorUserID = Common.CookiesManagement.GetUserModel().UserID;
            if (dt.Rows.Count>0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6DetailsModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                m.BeginDate = Convert.ToDateTime(m.BeginDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.BeginDate).ToString("yyyy-MM-dd");
                m.EndDate = Convert.ToDateTime(m.EndDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.EndDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=5 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsEditAsync(Bill6DetailsModel m)
        {
            Result<int> result = Web.Common.Bill6.BillDetailsEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillProceduresDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails6 where BillProcedureID in (" + IDs + @")");
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillProcedures6 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails6 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult BillDetailsStuffList(int BillDetailsID)
        {
            ViewBag.ID = BillDetailsID;
            return View();
        }

        [HttpPost]
        public string BillDetailsStuffListAsync(int BillDetailsID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillDetailsID",SqlDbType.Int),
            };
            parameters[0].Value = BillDetailsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill6DetailsStuffList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BillDetailsStuffEdit(int BillDetailsID,int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select a.Qty,a.GoodsID,b.TypeID,a.ID
FROM BillDetailsStuff6 as a inner join Goods b on a.GoodsID=b.ID
WHERE a.ID=" + ID).Tables[0];
            ViewBag.BillDetailsID = BillDetailsID;
            ViewBag.ID = ID;
            ViewBag.Qty = 0;
            ViewBag.GoodsID = 0;
            ViewBag.GoodsType = 1;
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            if (dt.Rows.Count > 0)
            {
                ViewBag.Qty = dt.Rows[0]["Qty"].ToString();
                ViewBag.GoodsID = dt.Rows[0]["GoodsID"].ToString();
                ViewBag.GoodsType = dt.Rows[0]["TypeID"].ToString();
            }
            ViewBag.model = m;




            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsStuffEditAsync(int BillDetailsID,int GoodsID,int Qty,int ID)
        {
            Result<string> result = new Result<string>();
            try
            {
                if (ID == 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillDetailsStuff6]
           ([BillDetailsID]
           ,[GoodsID]
           ,[Qty])
     VALUES
           (
'" + BillDetailsID + @"',
'" + GoodsID + @"',
'" + Qty + @"'
)
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
                else
                {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
UPDATE [dbo].[BillDetailsStuff6]
   SET [BillDetailsID] = '" + BillDetailsID + @"'
      ,[GoodsID] = '" + GoodsID + @"'
      ,[Qty] = '" + Qty + @"'
 WHERE ID=" + ID + @"
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsStuffDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetailsStuff6 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult JiaoFuList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select bi.ID,bi.Code,bi.BiaoZhun,bi.CreateDate,bi.Number,bi.Qty,po.WaiXing,po.XuFangDaiHao,po.Code as JiHuaHao,po.DengJi,po.GuiFanHao,po.HeTongCode
		,po.HeTongQty as HeTongCount,bi.JiHuaOrderID,po.Code as JiHuaOrderCode
	,po.GoodsID,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
FROM BillIndex6 as bi inner join PlannedOrder as po on bi.JiHuaOrderID=po.ID 
	left join Goods on po.GoodsID=Goods.ID 
WHERE bi.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string JiaoFuListAsync(int IndexID)
        {

            GridResult result = new GridResult();
            
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select ID,CONVERT(nvarchar(50),CreateTime,120) as CreateTime,Qty ,Number,Remark
from GoodsStockRunnings where BillNameID=6 and Type=1 and BillIndexID=" + IndexID+@"
order by ID desc").Tables[0];
            result.count =0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult JiaoFuEdit(int IndexID, int ID = 0)
        {
            ViewBag.IndexID = IndexID;
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select * from GoodsStockRunnings WHERE ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.JiaoFuModel m = new Models.MVC.JiaoFuModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.JiaoFuModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateTime = Convert.ToDateTime(m.CreateTime).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult JiaoFuEditAsync(int IndexID, decimal Qty, int ID = 0, string Remark = "", string Number = "", int IsExec = 0, string CreateTime = "")
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            result = Web.Common.BillCommon.JiaoFuEditAsync(6, IndexID, Qty, ID, Remark, Number, IsExec, CreateTime);

            return Json(result);
        }
        [HttpPost]
        public ActionResult ShowIndexEditAsync(int IndexID, int BillProcedureID, int ShowIndex)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures6 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures6 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                }

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillProcedures6 set ShowIndex=" + ShowIndex + " where ID=" + BillProcedureID);
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }
        public ActionResult ReportChengPinLv()
        {
            ViewBag.TableClo = FreeADO.DBUtility.DbHelperSQL.GetSingle(@"select '{ field: ''C_'+Convert(nvarchar(50),ID)+'_FailQty'', title: '''+Name+'淘汰'' },'
from  [Procedures] where  [Procedures].BillNameID=6 and IsDeleted=0 for xml path('')").ToString();
            return View();
        }

        [HttpPost]
        public string ReportChengPinLvAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = BeginDate;
            parameters[4].Value = EndDate;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill6ReportChengPinLv", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }




        public ActionResult GoodsList()
        {
            return View();
        }
        [HttpPost]
        public string GoodsListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "")
        {

            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@TypeID",SqlDbType.Int),
                                            new SqlParameter("@State",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@Type",SqlDbType.Int),
                                            new SqlParameter("@BillNameID",SqlDbType.Int)};
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = 3;
            parameters[3].Value = 1;
            parameters[4].Value = Search;
            parameters[5].Value = 2;
            parameters[6].Value = 6;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryGoodsSelect", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BOMDetails(int MainGoodsID)
        {
            ViewBag.MainGoodsID = MainGoodsID;
            return View();
        }
        [HttpPost]
        public string BOMDetailsAsync(int MainGoodsID = 0)
        {

            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@MainGoodsID",SqlDbType.Int)};
            parameters[0].Value = MainGoodsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBOMDetailsForBill6", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }


        public ActionResult BOMDetailsEdit(int ID = 0, int MainGoodsID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select a.ID,a.GoodsID as SourceGoodsID,a.Type,a.Qty,b.Name as SourceGoodsName ,b.Model as SourceGoodsModel,a.MainGoodsID
	from BillName6GoodBOM as a inner join Goods as b on a.GoodsID=b.ID
	where a.ID=" + ID).Tables[0];
            BOMDetailsEdit m = new BOMDetailsEdit();
            m.MainGoodsID = MainGoodsID;
            m.Type = 1;
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.BOMDetailsEdit>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];

            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult BOMDetailsEditAsync(BOMDetailsEdit m)
        {
            Result<int> result = new Result<int>();
            result.Data = 0;
            result.Type = AjaxResultType.Success;
            result.Content = "成功";
            if (m.ID == 0)
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6GoodBOM where MainGoodsID=" + m.MainGoodsID + " and Type=" + m.Type)) > 0 && m.Type != 3)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = m.Type == 1 ? "只能添加一个管座" : "只能添加一个管帽";
                    return Json(result);
                }
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6GoodBOM where MainGoodsID=" + m.MainGoodsID + " and GoodsID=" + m.SourceGoodsID)) > 0)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = "已经存在此原料";
                    return Json(result);
                }
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"insert into BillName6GoodBOM(MainGoodsID,Type,GoodsID,Qty) values(" + m.MainGoodsID + "," + m.Type + "," + m.SourceGoodsID + "," + m.Qty + ")");
            }
            else
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6GoodBOM where ID <>" + m.ID + " and MainGoodsID=" + m.MainGoodsID + " and Type=" + m.Type)) > 0 && m.Type != 3)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = m.Type == 1 ? "只能添加一个管座" : "只能添加一个管帽";
                    return Json(result);
                }
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6GoodBOM where ID <>" + m.ID + " and  MainGoodsID=" + m.MainGoodsID + " and GoodsID=" + m.SourceGoodsID)) > 0)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = "已经存在此原料";
                    return Json(result);
                }
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"update BillName6GoodBOM set Type=" + m.Type + ",GoodsID=" + m.SourceGoodsID + ",Qty=" + m.Qty + " where ID=" + m.ID);
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BOMDetailsDelAsync(string IDs)
        {
            Result<int> result = new Result<int>();
            result.Data = 0;
            result.Type = AjaxResultType.Success;
            result.Content = "成功";

            FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"delete BillName6GoodBOM  where ID in(" + IDs + ")");
            return Json(result);
        }



        public ActionResult BillBOMDetails(int BillID)
        {
            ViewBag.BillID = BillID;
            return View();
        }
        [HttpPost]
        public string BillBOMDetailsAsync(int BillID = 0)
        {

            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillID",SqlDbType.Int)};
            parameters[0].Value = BillID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillBOMDetails", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public ActionResult BillBOMDetailsEdit(int ID = 0, int BillID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select a.*,a.GoodsID as SourceGoodsID,b.Name as SourceGoodsName ,b.Model as SourceGoodsModel
	from BillName6BillBOM as a inner join Goods as b on a.GoodsID=b.ID
	where a.ID=" + ID).Tables[0];
            BillBOMDetailsEdit m = new BillBOMDetailsEdit();
            m.Type = 1;
            m.BillID = BillID;
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.BillBOMDetailsEdit>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];

            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult BillBOMDetailsEditAsync(BillBOMDetailsEdit m)
        {
            Result<int> result = new Result<int>();
            result.Data = 0;
            result.Type = AjaxResultType.Success;
            result.Content = "成功";
            if (m.ID == 0)
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6BillBOM where BillID=" + m.BillID + " and Type=" + m.Type)) > 0 && m.Type != 3)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = m.Type == 1 ? "只能添加一个管座" : "只能添加一个管帽";
                    return Json(result);
                }
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6BillBOM where BillID=" + m.BillID + " and GoodsID=" + m.SourceGoodsID)) > 0)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = "已经存在此原料";
                    return Json(result);
                }
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillName6BillBOM]
           ([BillID]
           ,[Type]
           ,[GoodsID]
           ,[Qty]
           ,[LingQty]
           ,[HuoYuanHao]
           ,[JianYanPiHao]
           ,[ZhiZhaoChangShang]
           ,[XinPianMianJi]
           ,[ZhengMianJinShu]
           ,[BeiMianJinShu])
     VALUES
           ('" + m.BillID + @"'
           ,'" + m.Type + @"'
           ,'" + m.SourceGoodsID + @"'
           ,'" + m.Qty + @"'
           ,'" + m.LingQty + @"'
           ,'" + m.HuoYuanHao + @"'
           ,'" + m.JianYanPiHao + @"'
           ,'" + m.ZhiZhaoChangShang + @"'
           ,'" + m.XinPianMianJi + @"'
           ,'" + m.ZhengMianJinShu + @"'
           ,'" + m.BeiMianJinShu + @"'
)");
            }
            else
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6BillBOM where ID <>" + m.ID + " and BillID=" + m.BillID + " and Type=" + m.Type)) > 0 && m.Type != 3)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = m.Type == 1 ? "只能添加一个管座" : "只能添加一个管帽";
                    return Json(result);
                }
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillName6BillBOM where ID <>" + m.ID + " and  BillID=" + m.BillID + " and GoodsID=" + m.SourceGoodsID)) > 0)
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = "已经存在此原料";
                    return Json(result);
                }
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
update BillName6BillBOM 
set Type=" + m.Type + @"
,GoodsID=" + m.SourceGoodsID + @"
,Qty=" + m.Qty + @"
,LingQty=" + m.LingQty + @" 
,HuoYuanHao='" + m.HuoYuanHao + @"'
,JianYanPiHao='" + m.JianYanPiHao + @"'
,ZhiZhaoChangShang='" + m.ZhiZhaoChangShang + @"'
,XinPianMianJi='" + m.XinPianMianJi + @"'
,ZhengMianJinShu='" + m.ZhengMianJinShu + @"'
,BeiMianJinShu='" + m.BeiMianJinShu + @"'
where ID=" + m.ID);
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillBOMDetailsDelAsync(string IDs)
        {
            Result<int> result = new Result<int>();
            result.Data = 0;
            result.Type = AjaxResultType.Success;
            result.Content = "成功";

            FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"delete BillName6BillBOM  where ID in(" + IDs + ")");
            return Json(result);
        }

        public ActionResult ReportForBill6()
        {
            return View();
        }

        [HttpPost]
        public string ReportForBill6Async(string BeginDate, string EndDate, int PageIndex = 1, int PageSize = 1000000000)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int)
            };
            parameters[0].Value = BeginDate;
            parameters[1].Value = EndDate;
            parameters[2].Value = PageIndex;
            parameters[3].Value = PageSize;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ReportBill6List", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public FileResult Down(int ID)
        {


            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
            select bom.GoodsID
                    ,g.Name as GoodsName
                    ,g.Model as GoodsModel
                    ,bom.HuoYuanHao
                    ,bom.JianYanPiHao
                    ,bom.ZhiZhaoChangShang
                    ,bom.XinPianMianJi
                    ,bom.ZhengMianJinShu
                    ,bom.BeiMianJinShu
                    ,bom.Qty
                    ,bom.LingQty
                    ,Case bom.Type when  1 then '管座' when 2 then '盖板' else '芯片' end Type
            from BillBOM as bom inner join Goods as g on bom.GoodsID=g.ID
            where BillID=" + ID + @"
            order by bom.Type
            ").Tables[0];

            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select po.Code as JiHuaHao,po.Number,po.HeTongCode,po.JiHuaQty
		,g.Name as GoodsName,g.Model as GoodsModel,po.XuFangDaiHao,po.DengJi,po.WaiXing,po.HeTongQty as HeTongCount,Convert(nvarchar(10),po.OrderDate,120) as OrderDate
		,Convert(nvarchar(10),po.JiaoFuDate,120) as JiaoFuDate,po.GuiFanHao,po.WenJianBianHao,po.JianZhiYanShou,po.QiTa
FROM PlannedOrder as po left join Goods as g on po.GoodsID=g.ID 
where po.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            if (dt1.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
            }
            MemoryStream ms = Common.ExcelTool.Bill6ToExcel(dt, m);
            return File(ms, "application/vnd.ms-excel", m.JiHuaHao + ".xls");
        }

        public FileResult Down2(int ID)
        {

            int JiHuaOrderID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select JiHuaOrderID from BillIndex6 where ID="+ID));
            DataTable bomdt = FreeADO.DBUtility.DbHelperSQL.Query(@"
            select bom.GoodsID
                    ,g.Name as GoodsName
                    ,g.Model as GoodsModel
                    ,bom.HuoYuanHao
                    ,bom.JianYanPiHao
                    ,bom.ZhiZhaoChangShang
                    ,bom.XinPianMianJi
                    ,bom.ZhengMianJinShu
                    ,bom.BeiMianJinShu
                    ,bom.Qty
                    ,bom.LingQty
                    ,bom.Type
            from BillBOM as bom inner join Goods as g on bom.GoodsID=g.ID
            where BillID=" + JiHuaOrderID + @"
            order by bom.Type
            ").Tables[0];

            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select pro.Name as ProName,SheBei,GongYi,CheckRemark,InQty,OutQty,FailQty,EndDate,u.Name as UserName
from BillDetails6 as bd inner join BillProcedures6 as bp on bd.BillProcedureID=bp.ID
	inner join [Procedures] as pro on bp.ProcedureID=pro.ID
	left join Users as u on bd.OperatorUserID=u.ID
where bp.BillIndexID=" + ID + @" 
order by bp.ShowIndex
").Tables[0];
            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select bi.Code,po.Code as JiHuaHao,bi.Number,po.HeTongCode,Convert(nvarchar(10),bi.CreateDate,120) as CreateDate
		,g.Name as GoodsName,g.Model as GoodsModel,po.XuFangDaiHao,po.DengJi,po.WaiXing,po.HeTongQty as HeTongCount
		,bi.Qty
        ,isnull((select Convert(nvarchar(10),max(gsr.CreateTime),120) from GoodsStockRunnings as gsr where gsr.BillIndexID=bi.ID and BillNameID=6 and Type=1),'') as JiaoFuDate
		,isnull((select top 1 gg.Model from GoodsStockRunnings as gsr inner join Goods as gg on gsr.GoodsID=gg.ID where gsr.BillIndexID=bi.ID and BillNameID=6 and Type=1),'') as JiaoFuModel
		,isnull((select sum(Qty) from GoodsStockRunnings as gsr where gsr.BillIndexID=bi.ID and BillNameID=6 and Type=1),0) as JiaoFuQty
		,isnull((select sum(bd.FailQty) from BillDetails6 as bd inner join BillProcedures6 as bp on bd.BillProcedureID=bp.ID where bp.BillIndexID=bi.ID ),0) as FailQty
        ,isnull((select g.Model+' ' from BillBOM as a inner join Goods as g on a.GoodsID=g.ID where a.BillID=bi.JiHuaOrderID and a.Type=3 for xml path('')),'') as SourceGoodsModel
        ,isnull((select g.Model+' ' from BillBOM as a inner join Goods as g on a.GoodsID=g.ID where a.BillID=bi.JiHuaOrderID and a.Type in(1,2) for xml path('')),'') as GuanKe
        ,po.QiTa
FROM BillIndex6 as bi inner join PlannedOrder as po on bi.JiHuaOrderID=po.ID 
	left join Goods as g on po.GoodsID=g.ID 
where bi.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill6IndexModel m = new Models.MVC.Bill6IndexModel();
            if (dt1.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill6IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
            }
            MemoryStream ms = Common.ExcelTool.Bill6ToExcel2(bomdt,dt, m);
            return File(ms, "application/vnd.ms-excel", m.Code + ".xls");
        }


    }
}